package com.xinhe.web.reposity;

import com.xinhe.web.entity.Member;
import com.xinhe.web.response.MemberAccountDetailsView;
import com.xinhe.web.response.MemberView;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;


public interface MemberRepository extends JpaRepository<Member, Long> {
    @Query("from Member where removed =0 and fUserId = ?1")
    Member findByUserId(Long userId);
    @Query("from Member where removed =0 and mobile = ?1")
    Member findByMobile(String mobile);
    @Query("from Member where removed =0 and parentCode = ?1")
    Member findByCode(String parentCode);

    @Query(value = "select name from tb_member where removed=0 and code=?1", nativeQuery = true)
    Member findMemberByCode(String code);

    @Query(value = "select count(1) from tb_member where removed=0 and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')", nativeQuery = true)
    int findByThisMonthMember();
    @Query(value = "select count(1) from tb_member where removed=0 and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and f_user_id =?1", nativeQuery = true)
    int findByThisMonthMember(Long fUserId);

    @Query(value = "select * from tb_member where removed=0 and f_service_id=?1", nativeQuery = true)
    List<Member> findByServiceId(Long serviceId);
    @Query(value = "select m.mobile,m.name,m.adoption_num adoptionNum," +
            "(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='RMB') rmb," +
            "(select ifnull(sum(remainder),0) from tb_asset_count where f_user_id=m.f_user_id and (type='YUN_CHA_SEED' or type='NEW_YUN_CHA_SEED')) toThawedYunChaSeed," +
            "(select ifnull(sum(trade_amount),0) from tb_asset_detail where f_user_id=m.f_user_id and trade_type='ADOPTED_TREE' and (asset_type='YUN_CHA_SEED' or asset_type='NEW_YUN_CHA_SEED')) sumYunChaSeed," +
            "(select ifnull(sum(trade_amount),0) from tb_asset_detail where f_user_id=m.f_user_id and (trade_type='THREE_THAW_YUN_CHA_SEED' or trade_type='TWO_THAW_YUN_CHA_SEED' or trade_type='SYSTEM_THAW_YUN_CHA_SEED') and asset_type='RMB') thawedTunChaSeed," +
            "(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='TEA_OIL_CARD') teaOilCard," +
            "(select ifnull(sum(trade_amount),0) from tb_asset_detail where f_user_id=m.f_user_id and trade_type='ADOPTED_OIL_CARD' and asset_type='TEA_OIL_CARD')sumTeaOilCard," +
            "(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='CONSUMER_GOLD_RED_ENVELOPE') toThawedConsumerGoldRedEnvelope," +
            "(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='UNFREEZING_CONSUMER_GOLD_RED_ENVELOPE') thawedConsumerGoldRedEnvelope," +
            "(select ifnull(sum(trade_amount),0) from tb_asset_detail where f_user_id=m.f_user_id and trade_type='ADOPTED_CONSUMER_GOLD_RED_ENVELOPE' and asset_type='CONSUMER_GOLD_RED_ENVELOPE') sumConsumerGoldRedEnvelope," +
            "(select ifnull(sum(money),0) from tb_withdrawals_record where removed = 0 and status='SUCCESS' and f_user_id =m.f_user_id) withdrawalsMoney" +
            " from tb_member m where removed=0 and f_user_id =?1", nativeQuery = true)
    MemberAccountDetailsView findByServiceIdAndAll(Long fUserId);

    @Query(value = "select code,name,mobile,level,role,role_two roleTwo,(select name from tb_member m1 where m1.code=m.parent_code)parentName," +
            "(select IFNULL(sum(amount),0) from tb_adopted_tree_order where f_user_id=m.f_user_id and status='PAY'" +
            "and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ") adoptionNum,(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='RMB') balance," +
            "(select IFNULL(sum(order_fee),0) from tb_adopted_tree_order where f_user_id=m.f_user_id and status='PAY'" +
            "and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            " )adoptedSumMoney,(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='TEA_OIL_CARD') teaOilCardBalance," +
            "(select ifnull(sum(remainder),0) from tb_asset_count where f_user_id=m.f_user_id and (type='YUN_CHA_SEED' or type='NEW_YUN_CHA_SEED')) yunChaSeedBalance," +
            "(select IFNULL(sum(money),0) from tb_withdrawals_record where f_user_id=m.f_user_id and status='TRANSFER_ACCOUNTS_SUCCESS'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ") withdrawnAmount,(select count(1) from tb_member m1 where m1.parent_code=m.code)twoThawNo," +
            "(select ifnull(sum((select count(1) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.`code`)threeThawNo,create_time createTime, " +
            "(select IFNULL(sum((select sum(amount) from tb_adopted_tree_order where f_user_id=m1.f_user_id and status='PAY'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")),0)  from tb_member m1 where m1.parent_code=m.code) twoThawNum," +
            "(select IFNULL(sum((select sum(order_fee) from tb_adopted_tree_order where f_user_id=m1.f_user_id and status='PAY'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")),0)  from tb_member m1 where m1.parent_code=m.`code`) twoThawMoney," +
            "(select ifnull(sum((select sum((select sum(amount) from tb_adopted_tree_order where f_user_id=m2.f_user_id and status='PAY' " +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.code)threeThawNum," +
            "(select ifnull(sum((select sum((select sum(order_fee) from tb_adopted_tree_order where f_user_id=m2.f_user_id and status='PAY' " +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.`code`) threeThawMoney " +
            " from tb_member m where removed=0 and (?1 IS NULL OR mobile=?1)" +
            "and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)", nativeQuery = true)
    List<Map<String,Object>> findByMemberMobileStatistics2(String mobile, Date startTime, Date endTime);

    @Query(value = "select code,name,mobile,level,role,role_two roleTwo,(select name from tb_member m1 where m1.code=m.parent_code)parentName," +
            "(select IFNULL(sum(amount),0) from tb_adopted_tree_order where f_user_id=m.f_user_id and status='PAY'" +
            "and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ") adoptionNum,(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='RMB') balance," +
            "(select IFNULL(sum(order_fee),0) from tb_adopted_tree_order where f_user_id=m.f_user_id and status='PAY'" +
            "and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            " )adoptedSumMoney,(select remainder from tb_asset_count where f_user_id=m.f_user_id and type='TEA_OIL_CARD') teaOilCardBalance," +
            "(select ifnull(sum(remainder),0) from tb_asset_count where f_user_id=m.f_user_id and (type='YUN_CHA_SEED' or type='NEW_YUN_CHA_SEED')) yunChaSeedBalance," +
            "(select IFNULL(sum(money),0) from tb_withdrawals_record where f_user_id=m.f_user_id and status='TRANSFER_ACCOUNTS_SUCCESS'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ") withdrawnAmount,(select count(1) from tb_member m1 where m1.parent_code=m.code)twoThawNo," +
            "(select ifnull(sum((select count(1) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.code)threeThawNo,create_time createTime, " +
            "(select IFNULL(sum((select sum(amount) from tb_adopted_tree_order where f_user_id=m1.f_user_id and status='PAY'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")),0)  from tb_member m1 where m1.parent_code=m.code) twoThawNum," +
            "(select IFNULL(sum((select sum(order_fee) from tb_adopted_tree_order where f_user_id=m1.f_user_id and status='PAY'" +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")),0)  from tb_member m1 where m1.parent_code=m.code) twoThawMoney," +
            "(select ifnull(sum((select sum((select sum(amount) from tb_adopted_tree_order where f_user_id=m2.f_user_id and status='PAY' " +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.code)threeThawNum," +
            "(select ifnull(sum((select sum((select sum(order_fee) from tb_adopted_tree_order where f_user_id=m2.f_user_id and status='PAY' " +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3)" +
            ")) from tb_member m2 where m2.parent_code=m1.code)),0) from tb_member m1 where m1.parent_code=m.code) threeThawMoney " +
            " from tb_member m where removed=0 and (?1 IS NULL OR mobile=?1) " +
            " and (?2 IS NULL OR create_time >=?2) and (?3 IS NULL OR create_time <=?3) LIMIT ?4,?5", nativeQuery = true)
    List<Map<String,Object>> findByMemberMobileStatistics(String mobile, Date startTime, Date endTime,Integer pageNum,Integer pageSize);
    /*
    在这个查询语句中，"SELECT FOUND_ROWS()" 是一个MySQL特有的函数。这个函数用于返回上一个执行的查询的结果集中的总记录数，并且在查询中使用了"nativeQuery = true"，表示使用原生SQL查询语句。
    当一个MySQL查询语句中包含LIMIT关键字时，查询的结果集只会返回指定范围内的记录。但是，如果使用"SELECT FOUND_ROWS()"来查询总记录数，MySQL会返回完整结果集的总记录数，而不是限制范围内的记录数。
    因此，使用"SELECT FOUND_ROWS()"语句可以查出分页记录的总记录数。在这个例子中，int getTotal() 方法将返回查询结果中的总记录数。
    * */
    @Query(value = "SELECT FOUND_ROWS()", nativeQuery = true)
    int getTotal();
    @Query(value = "select name from tb_member where removed=0 and code=?1", nativeQuery = true)
    String queryParentName(String parentCode);
    @Query(value = "select parent_code from tb_member where removed=0 and code=?1", nativeQuery = true)
    String queryParentCode(String code);
    @Query(value = "select get_root_name(?1) as rootName", nativeQuery = true)
    String queryFunder(String code);

    @Query(value = "select * from tb_member m where removed = 0 and f_user_id in (select f_user_id from tb_asset_count where removed = 0 and f_user_id = m.f_user_id and type = 'YUN_CHA_SEED' and remainder > 0", nativeQuery = true)
    List<Member> thawYunChaSeed();

    @Query(value = "select * from tb_member m where removed = 0 and f_user_id in (select f_user_id from tb_asset_count where removed = 0 and f_user_id = m.f_user_id and type = 'NEW_YUN_CHA_SEED' and remainder > 0)", nativeQuery = true)
    List<Member> thawNewYunChaSeed();

    @Query(value = "select * from tb_member m where removed = 0 and f_user_id in (select f_user_id from tb_asset_count where removed = 0 and f_user_id = m.f_user_id and type = 'CONSUMER_GOLD_RED_ENVELOPE' and remainder > 0)", nativeQuery = true)
    List<Member> thawConsumerGoldRedEnvelope();
    @Query(value = "select count(1) from tb_member where to_days(create_time)=to_days(now())", nativeQuery = true)
    Integer findByTodayMember();
    @Query(value = "select count(1) from tb_member where removed=0 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1", nativeQuery = true)
    Integer findByYesterday();
    @Query(value = "select count(1) from tb_member where removed=0 and adoption_num >0", nativeQuery = true)
    Integer findByPayAndNoPay0(int i);
    @Query(value = "select count(1) from tb_member where removed=0 and adoption_num <=0", nativeQuery = true)
    Integer findByPayAndNoPay1(int i);
    @Query(value = "select * from tb_member where removed=0 and parent_code=?1", nativeQuery = true)
    List<Member> findBySubordinateCode(String code);

    @Query(value = "select * from tb_member where removed=0 and role='FOUNDER'", nativeQuery = true)
    List<Member> findFounderMember1();
    @Query(value = "select * from tb_member where removed=0 and role='FOUNDER' and f_service_id=?1", nativeQuery = true)
    List<Member> findFounderMember2(Long serviceId);

    @Query(value = "select DATE_FORMAT(create_time,'%Y-%m-%d') time ,count(1) amount from tb_member WHERE removed=0 and DATE(create_time)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) GROUP BY time", nativeQuery = true)
    List<Map<String,String>> findByBranchDataStatistics();
    @Query(value = "select * from tb_member where removed=0 and mobile=?1 and password=?2", nativeQuery = true)
    Member findByMobileAndPassword(String mobile, String pwd);
    @Query(value = "select mobile from tb_member where removed=0 and code=?1", nativeQuery = true)
    String queryParentMobile(String parentCode);
    @Query(value = "select (select count(1) from tb_member m2 where m2.parent_code=m.code) directPush,(select sum((select count(1) from tb_member m3 where m3.parent_code=m2.code))  from tb_member m2 where m2.parent_code=m.code ) Interpenetration from tb_member m where removed=0 and  f_user_id=?1", nativeQuery = true)
    Map<String,Integer> findByDirectPushAndInterpenetration(Long userId);

    @Query(value = "select DISTINCT m2.f_user_id userId,m2.nickname,m2.icon_path iconPath,m2.create_time createTime from tb_member m left join tb_member m2 on m2.parent_code=m.code where m.f_user_id=?1", nativeQuery = true)
    List<Map<String,Object>> findByParentCode(Long fUserId);

    @Query(value = "select DISTINCT m3.f_user_id userId,m3.nickname,m3.icon_path iconPath,m3.create_time createTime from tb_member m left join tb_member m2 on m2.parent_code=m.code RIGHT join tb_member m3 on m3.parent_code=m2.code where m.f_user_id=?1", nativeQuery = true)
    List<Map<String,Object>> findByInterpenetration(Long fUserId);

    @Query(value = "select * from tb_member where removed=0 and new_reference=?1", nativeQuery = true)
    List<Member> findByNewReferenceCode(String code);
}